package top.withwings.bonus.bonus.repo;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;
import top.withwings.bonus.bonus.pojo.FinalBonus;
import top.withwings.bonus.bonus.pojo.Rate;
import top.withwings.bonus.bonus.util.BonusDateUtils;
import top.withwings.bonus.common.pojo.Period;
import top.withwings.bonus.common.pojo.ReinforcedBeanPropertySqlParameterSource;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


@Repository
public class FinalBonusRepo {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;


    public FinalBonus gather(String yearMonth, int salesmanId) {
        Period period = BonusDateUtils.fromYearMonth(yearMonth);

        String sql =
                "SELECT "
                        + " ? as `year_month`, "
                        + " b.salesman_id, "
                        + " ifnull(sum(b.amount) ,0) as gross_amount, "
                        + " ifnull(sum(b.make_up / 100 * amount) ,0 )as make_up_sum ,"
                        + " ifnull(sum(b.final_rate_value / 100 * amount) ,0 )as single_sum "
                        + "  FROM bonus_form b inner join good g on b.good_id = g.id"
                        + " where b.salesman_id = ? and b.effective_date between ? and ?";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(FinalBonus.class), yearMonth,salesmanId, period.getStart(), period.getEnd());
        } catch (EmptyResultDataAccessException e) {
            return null;
        }

    }


    public void delete(int salesmanId, String yearMonth) {
        String sql = "delete from final_bonus where salesman_id = ? and `year_month` = ?";
        jdbcTemplate.update(sql, salesmanId,yearMonth);
    }

    public void save(List<FinalBonus> finalBonuses) {
        String sql =
                " insert into final_bonus( "
                        + " `year_month`, "
                        + " salesman_id, "
                        + " gross_amount, "
                        + " single_sum, "
                        + " make_up_sum, "
                        + " final_result, "
                        + " gross_amount_rate_value"
                        + " )values( "
                        + " :yearMonth, "
                        + " :salesmanId, "
                        + " :grossAmount, "
                        + " :singleSum, "
                        + " :makeUpSum, "
                        + " :finalResult, "
                        + " :grossAmountRateValue "
                        + " ) ";

        ArrayList<SqlParameterSource> sqlParameterSources = new ArrayList<>();

        for (FinalBonus finalBonus : finalBonuses) {

            Map<String, Object> map = new HashMap<>();
            map.put("salesmanId", finalBonus.getSalesman().getId());
            map.put("grossAmountRateValue", finalBonus.getGrossAmountRate().getValue());


            SqlParameterSource parameterSource = new ReinforcedBeanPropertySqlParameterSource(finalBonus, map);

            sqlParameterSources.add(parameterSource);

        }

        namedParameterJdbcTemplate.batchUpdate(sql, sqlParameterSources.toArray(new SqlParameterSource[sqlParameterSources.size()]));

    }

    public FinalBonus findOne(int salesmanId, String yearMonth) {
        String sql = " select  "
                + " `year_month`, "
                + " salesman_id, "
                + " gross_amount, "
                + " single_sum, "
                + " make_up_sum, "
                + " final_result, "
                + " gross_amount_rate_value"
                + " from final_bonus  "
                + " where `year_month` = ? and salesman_id =  ?";

        try {
            return jdbcTemplate.queryForObject(sql, new FinalBonusRowMapper(), yearMonth, salesmanId);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }

    }

    private static class FinalBonusRowMapper implements RowMapper<FinalBonus> {

        private final RowMapper<FinalBonus> basicMapper = new BeanPropertyRowMapper<>(FinalBonus.class);

        @Override
        public FinalBonus mapRow(ResultSet rs, int rowNum) throws SQLException {
            FinalBonus finalBonus = basicMapper.mapRow(rs, rowNum);
            finalBonus.setGrossAmountRate(new Rate(Rate.TYPE_NORMAL, rs.getDouble("gross_amount_rate_value")));
            return finalBonus;
        }
    }


}
